Imports DAL
Imports MyClasses
Imports System.Data.SqlClient
Imports System.Text
Imports System.Configuration
Public Class DProdTrans    
    Public Shared Function GetTrans(ByVal objProdTrans As CProdTrans) As DataTable
        Try
            Dim dbConnection As New dbAccess
            Dim sbSql As StringBuilder = New StringBuilder()
            Dim intFinYrID As Integer = ConfigurationManager.AppSettings("FinYrId")
            Dim strFromDt As String = Format(objProdTrans.FromDate, "yyyy/MM/dd")
            Dim strToDt As String = Format(objProdTrans.ToDate, "yyyy/MM/dd")
            Select Case (objProdTrans.Operation)
                ' Added Following single query for DC Record
                Case "1"

                    sbSql.Append(" SELECT Td.DCDate as Date, mv.vendorname as Description, (tid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(tid.Qty * tid.PurchaseRate as float) as InValue, 0 as OutValue " & _
                             " FROM  tDC Td, tdcdet tid, mpItem mi, mpvendor mv " & _
                             " WHERE td.DCno = tid.DCno " & _
                             " AND tid.ItemID = mi.ItemID " & _
                             " AND Td.VendorID = mv.VendorID AND td.DCno not in (SELECT DCNO FROM tPurchaseInvoice) " & _
                             " AND Td.DCDate > = " & "'" & strFromDt & "'" & _
                             " AND Td.DCDate < = " & "'" & strToDt & "'" & _
                             " AND Td.FinYrID=" & intFinYrID & " AND tid.FinYrID= " & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "td", "tid"))

                Case "2"

                    sbSql.Append(" SELECT PI.InvoiceDate as Date, mv.vendorname as Description, (pid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(pid.Qty * pid.NetRate as float) as InValue, 0 as OutValue " & _
                             " FROM  tpurchaseinvoice PI, tpurchaseinvoicedet pid, mpItem mi, mpvendor mv " & _
                             " WHERE PI.InvoiceID = pid.InvoiceID " & _
                             " AND pid.ItemID = mi.ItemID " & _
                             " AND PI.VendorID = mv.VendorID " & _
                             " AND PI.InvoiceDate > = " & "'" & strFromDt & "'" & _
                             " AND PI.InvoiceDate < = " & "'" & strToDt & "'" & _
                             " AND PI.FinYrID=" & intFinYrID & " AND pid.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "PI", "pid"))

                Case "3"
                    sbSql.Append(" SELECT tpur.ReturnDate as Date, mv.vendorname as Description, 0 as InQuantity, tpurd.Qty as OutQuantity, 0 as InValue, tpurd.Amount as OutValue " & _
                             " FROM tpurreturn tpur, tpurreturndet tpurd, mpvendor mv, mpItem mi" & _
                             " WHERE tpur.PurReturnID = tpurd.PurReturnID" & _
                             " AND tpur.VendorID = mv.VendorID" & _
                             " AND mi.ItemID = tpurd.ItemID" & _
                             " AND tpur.ReturnDate  >= " & " '" & strFromDt & "'" & _
                             " AND tpur.ReturnDate  < = " & "'" & strToDt & "'" & _
                             " AND tpur.FinYrID=" & intFinYrID & " AND tpurd.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "tpur", "tpurd"))

                Case "4"
                    sbSql.Append(" SELECT ts.SaleDate as Date, case when ts.CustID=0 then 'CounterSale' else p.FName + ' ' + p.LName end as Description, 0 as InQuantity, sd.Qty as OutQuantity, 0 as InValue, sd.Amount as OutValue " & _
                             " FROM tsale ts, tsaledet sd, MpItem mi, mperson p, mcustomer mc, tstock st" & _
                             " WHERE ts.SaleID = sd.SaleID " & _
                             " AND sd.ItemID = mi.ItemID" & _
                             " AND mc.CustID = ts.CustID " & _
                             " AND p.personID = mc.personID " & _
                             " AND sd.ItemID = st.ItemID" & _
                             " AND st.ItemID = mi.ItemID " & _
                             " AND st.CLRcode = sd.CLRcode " & _
                             " AND ts.SaleDate >= " & " '" & strFromDt & "'" & _
                             " AND ts.SaleDate < = " & "'" & strToDt & "'" & _
                             " AND ts.FinYrID=" & intFinYrID & " AND sd.FinYrID=" & intFinYrID & " AND st.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "sd"))


                Case "5"
                    sbSql.Append(" SELECT tsr.SaleReturnDate as Date,'SaleReturn' as Description, tsrd.Qty as InQuantity, 0 as OutQuantity, tsrd.Amount as InValue, 0 as OutValue " & _
                             " FROM tsalereturn tsr, tsalereturndet as tsrd, MpItem mi,tstock st " & _
                             " WHERE tsr.SaleReturnID = tsrd.SaleReturnID " & _
                             " AND tsrd.ItemID = mi.ItemID " & _
                             " AND st.ItemID = mi.ItemID " & _
                             " AND tsrd.ItemID = st.ItemID " & _
                             " AND st.CLRcode = tsrd.CLRcode " & _
                             " AND tsr.SaleReturnDate  >= " & " '" & strFromDt & "'" & _
                             " AND tsr.SaleReturnDate  < = " & "'" & strToDt & "'" & _
                             " AND tsr.FinYrID=" & intFinYrID & " AND tsrd.FinYrID=" & intFinYrID & " AND st.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "tsrd"))

                Case "6"
                    sbSql.Append(" SELECT topen.OpeningDate as Date, 'OpeningStock' as Description, topen.Qty as InQuantity," & _
                             " 0 as OutQuantity, (Qty * Purchaserate) as Invalue, 0 as OutValue " & _
                             " FROM tOpeningStock topen,  mpItem mi" & _
                             " WHERE mi.itemID= topen.ItemID " & _
                             " AND OpeningDate >= " & " '" & strFromDt & "'" & _
                             " AND OpeningDate  < = " & "'" & strToDt & "'" & _
                             " AND topen.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "topen", "topen"))

                Case "-1", "0"

                    sbSql.Append(" SELECT PI.InvoiceDate as Date, mv.vendorname as Description, (pid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(pid.Qty * pid.NetRate as float) as InValue, 0 as OutValue " & _
                             " FROM  tpurchaseinvoice PI, tpurchaseinvoicedet pid, mpItem mi, mpvendor mv " & _
                             " WHERE PI.InvoiceID = pid.InvoiceID " & _
                             " AND pid.ItemID = mi.ItemID " & _
                             " AND PI.VendorID = mv.VendorID  " & _
                             " AND PI.InvoiceDate > = " & "'" & strFromDt & "'" & _
                             " AND PI.InvoiceDate < = " & "'" & strToDt & "'" & _
                             " AND PI.FinYrID=" & intFinYrID & " AND pid.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "PI", "pid"))

                    sbSql.Append(" UNION ALL SELECT Td.DCDate as Date, mv.vendorname as Description, (tid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(tid.Qty * tid.PurchaseRate as float) as InValue, 0 as OutValue " & _
                                      " FROM  tDC Td, tdcdet tid, mpItem mi, mpvendor mv " & _
                                      " WHERE td.DCno = tid.DCno " & _
                                      " AND tid.ItemID = mi.ItemID " & _
                                      " AND Td.VendorID = mv.VendorID  AND td.DCno not in (SELECT DCNO FROM tPurchaseInvoice)" & _
                                      " AND Td.DCDate > = " & "'" & strFromDt & "'" & _
                                      " AND Td.DCDate < = " & "'" & strToDt & "'" & _
                                      " AND Td.FinYrID=" & intFinYrID & " AND tid.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "td", "tid"))

                    sbSql.Append(" UNION ALL SELECT tpur.ReturnDate as Date, mv.vendorname as Description, 0 as InQuantity, tpurd.Qty as OutQuantity, 0 as InValue, tpurd.Amount as OutValue " & _
                                      " FROM tpurreturn tpur, tpurreturndet tpurd, mpvendor mv, mpItem mi" & _
                                      " WHERE tpur.PurReturnID = tpurd.PurReturnID" & _
                                      " AND tpur.VendorID = mv.VendorID" & _
                                      " AND mi.ItemID = tpurd.ItemID" & _
                                      " AND tpur.ReturnDate  >= " & " '" & strFromDt & "'" & _
                                      " AND tpur.ReturnDate  < = " & "'" & strToDt & "'" & _
                                      " AND tpur.FinYrID=" & intFinYrID & " AND tpurd.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "tpur", "tpurd"))

                    sbSql.Append(" UNION ALL SELECT ts.SaleDate as Date, case when ts.CustID=0 then 'CounterSale' else p.FName + ' ' + p.LName end as Description, 0 as InQuantity, sd.Qty as OutQuantity, 0 as InValue, sd.Amount as OutValue " & _
                                       " FROM tsale ts, tsaledet sd, MpItem mi, mperson p, mcustomer mc, tstock st" & _
                                       " WHERE ts.SaleID = sd.SaleID " & _
                                       " AND sd.ItemID = mi.ItemID" & _
                                       " AND mc.CustID = ts.CustID " & _
                                       " AND p.personID = mc.personID " & _
                                       " AND sd.ItemID = st.ItemID" & _
                                       " AND st.ItemID = mi.ItemID " & _
                                       " AND st.CLRcode = sd.CLRcode " & _
                                       " AND ts.SaleDate >= " & " '" & strFromDt & "'" & _
                                       " AND ts.SaleDate < = " & "'" & strToDt & "'" & _
                                       " AND ts.FinYrID=" & intFinYrID & " AND sd.FinYrID=" & intFinYrID & " AND st.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "sd"))

                    sbSql.Append(" UNION ALL SELECT tsr.SaleReturnDate as Date,'SalesReturn' as Description, tsrd.Qty as InQuantity,  0 as OutQuantity,  tsrd.Amount as InValue, 0 as OutValue " & _
                                      " FROM tsalereturn tsr, tsalereturndet as tsrd, MpItem mi,tstock st " & _
                                      " WHERE tsr.SaleReturnID = tsrd.SaleReturnID " & _
                                      " AND tsrd.ItemID = mi.ItemID " & _
                                      " AND st.ItemID = mi.ItemID " & _
                                      " AND tsrd.ItemID = st.ItemID " & _
                                      " AND st.CLRcode = tsrd.CLRcode " & _
                                      " AND tsr.SaleReturnDate  >= " & " '" & strFromDt & "'" & _
                                      " AND tsr.SaleReturnDate  < = " & "'" & strToDt & "'" & _
                                      " AND tsr.FinYrID=" & intFinYrID & " AND tsrd.FinYrID=" & intFinYrID & " AND st.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "tsrd"))

                    sbSql.Append(" UNION ALL SELECT topen.OpeningDate as Date, 'OpeningStock' as Description, topen.Qty as InQuantity," & _
                                      " 0 as OutQuantity, (Qty * Purchaserate) as Invalue, 0 as OutValue " & _
                                      " FROM tOpeningStock topen,  mpItem mi" & _
                                      " WHERE mi.itemID= topen.ItemID " & _
                                      " AND OpeningDate >= " & " '" & strFromDt & "'" & _
                                      " AND OpeningDate  < = " & "'" & strToDt & "'" & _
                                      " AND topen.FinYrID=" & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "topen", "topen"))

            End Select

            Return dbConnection.ExecuteDataTable(sbSql.ToString())


        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProdTrans", "GetTrans")
            Return Nothing
        End Try
    End Function
    Public Shared Function QueryBuild(ByVal objProdTrans As CProdTrans, ByVal Table1 As String, ByVal Table2 As String, ByVal Table3 As String) As String
        Try
            Dim sbSql As StringBuilder = New StringBuilder()
            If objProdTrans.Company <> 0 Then
                sbSql.Append(" AND " & Table1 & ".mfctrID = " & objProdTrans.Company)
            End If
            If objProdTrans.Vendor <> 0 Then
                sbSql.Append(" AND " & Table2 & ".VendorID = " & objProdTrans.Vendor)
            End If
            If objProdTrans.Product <> 0 Then
                sbSql.Append(" AND " & Table3 & ".ItemID = " & objProdTrans.Product)
            End If
            If objProdTrans.Batch <> "" Then
                sbSql.Append(" AND " & Table3 & ".CLRcode=" & "'" & objProdTrans.Batch & "'")
            End If

            Return sbSql.ToString()

        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProdTrans", "QueryBuild")
        End Try
    End Function
    Public Shared Function GetItems(ByVal Company As Integer) As DataTable
        'Returns The ItemList by Company
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT ItemID,ItemName" & _
                     " FROM mpItem WHERE mfctrID = " & Company
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProdTrans", "GetItems", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetItem(ByVal VendorID As Integer) As DataSet
        Try
            If VendorID <> 0 Then
                Return DbHelper.ExecuteDataSet(" SELECT Distinct mi.ItemID as ItemID, mi.ItemName as ItemName FROM mPitem mi, tRelation tr WHERE  mi.ItemID = tr.ItemID AND tr.VendorID =" & VendorID & " AND mi.Status='A' ORDER BY mi.ItemName")
            Else
                Return DbHelper.ExecuteDataSet("SELECT ItemID, ItemName FROM mPitem WHERE Status = 'A' ORDER BY ItemName")
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProdTrans", "GetItem")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetTrans1(ByVal objProdTrans As CProdTrans) As SqlDataReader
        Try
            Dim dbConnection As New dbAccess
            Dim sbSql As StringBuilder = New StringBuilder()
            Dim strFromDt As String = Format(objProdTrans.FromDate, "yyyy/mm/dd")
            Dim strToDt As String = Format(objProdTrans.ToDate, "yyyy/mm/dd")
            Dim intFinYrID As Integer = ConfigurationManager.AppSettings("FinYrId")
            Select Case (objProdTrans.Operation)
                ' Added Following single query for DC Record
                Case "1"

                    sbSql.Append(" SELECT Td.DCDate as Date, mv.vendorname as Description, (tid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(tid.Qty * tid.PurchaseRate as float) as InValue, 0 as OutValue " & _
                             " FROM  tDC Td, tdcdet tid, mpItem mi, mpvendor mv " & _
                             " WHERE td.DCno = tid.DCno " & _
                             " AND tid.ItemID = mi.ItemID " & _
                             " AND Td.VendorID = mv.VendorID AND td.DCno not in (SELECT DCNO FROM tPurchaseInvoice) " & _
                             " AND Td.DCDate > = " & "'" & strFromDt & "'" & _
                             " AND Td.DCDate < = " & "'" & strToDt & "'" & _
                             " AND Td.FinYrID=" & intFinYrID & " AND tid.FinYrID= " & intFinYrID & "")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "td", "tid"))

                Case "2"

                    sbSql.Append(" SELECT PI.InvoiceDate as Date, mv.vendorname as Description, (pid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(pid.Qty * pid.NetRate as float) as InValue, 0 as OutValue " & _
                             " FROM  tpurchaseinvoice PI, tpurchaseinvoicedet pid, mpItem mi, mpvendor mv " & _
                             " WHERE PI.InvoiceID = pid.InvoiceID " & _
                             " AND pid.ItemID = mi.ItemID " & _
                             " AND PI.VendorID = mv.VendorID " & _
                             " AND PI.InvoiceDate > = " & "'" & strFromDt & "'" & _
                             " AND PI.InvoiceDate < = " & "'" & strToDt & "'" & _
                             " AND PI.FinYrID=intFinYrID AND pid.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "PI", "pid"))

                Case "3"
                    sbSql.Append(" SELECT tpur.ReturnDate as Date, mv.vendorname as Description, 0 as InQuantity, tpurd.Qty as OutQuantity, 0 as InValue, tpurd.Amount as OutValue " & _
                             " FROM tpurreturn tpur, tpurreturndet tpurd, mpvendor mv, mpItem mi" & _
                             " WHERE tpur.PurReturnID = tpurd.PurReturnID" & _
                             " AND tpur.VendorID = mv.VendorID" & _
                             " AND mi.ItemID = tpurd.ItemID" & _
                             " AND tpur.ReturnDate  >= " & " '" & strFromDt & "'" & _
                             " AND tpur.ReturnDate  < = " & "'" & strToDt & "'" & _
                             " AND tpur.FinYrID=intFinYrID AND tpurd.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "tpur", "tpurd"))

                Case "4"
                    sbSql.Append(" SELECT ts.SaleDate as Date, case when ts.CustID=0 then 'CounterSale' else p.FName + ' ' + p.LName end as Description, 0 as InQuantity, sd.Qty as OutQuantity, 0 as InValue, sd.Amount as OutValue " & _
                             " FROM tsale ts, tsaledet sd, MpItem mi, mperson p, mcustomer mc, tstock st" & _
                             " WHERE ts.SaleID = sd.SaleID " & _
                             " AND sd.ItemID = mi.ItemID" & _
                             " AND mc.CustID = ts.CustID " & _
                             " AND p.personID = mc.personID " & _
                             " AND sd.ItemID = st.ItemID" & _
                             " AND st.ItemID = mi.ItemID " & _
                             " AND st.CLRcode = sd.CLRcode " & _
                             " AND ts.SaleDate >= " & " '" & strFromDt & "'" & _
                             " AND ts.SaleDate < = " & "'" & strToDt & "'" & _
                             " AND ts.FinYrID=intFinYrID AND sd.FinYrID=intFinYrID AND st.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "sd"))


                Case "5"
                    sbSql.Append(" SELECT tsr.SaleReturnDate as Date,'SaleReturn' as Description, tsrd.Qty as InQuantity, 0 as OutQuantity, tsrd.Amount as InValue, 0 as OutValue " & _
                             " FROM tsalereturn tsr, tsalereturndet as tsrd, MpItem mi,tstock st " & _
                             " WHERE tsr.SaleReturnID = tsrd.SaleReturnID " & _
                             " AND tsrd.ItemID = mi.ItemID " & _
                             " AND st.ItemID = mi.ItemID " & _
                             " AND tsrd.ItemID = st.ItemID " & _
                             " AND st.CLRcode = tsrd.CLRcode " & _
                             " AND tsr.SaleReturnDate  >= " & " '" & strFromDt & "'" & _
                             " AND tsr.SaleReturnDate  < = " & "'" & strToDt & "'" & _
                             " AND tsr.FinYrID=intFinYrID AND tsrd.FinYrID=intFinYrID AND st.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "tsrd"))

                Case "6"
                    sbSql.Append(" SELECT topen.OpeningDate as Date, 'OpeningStock' as Description, topen.Qty as InQuantity," & _
                             " 0 as OutQuantity, (Qty * Purchaserate) as Invalue, 0 as OutValue " & _
                             " FROM tOpeningStock topen,  mpItem mi" & _
                             " WHERE mi.itemID= topen.ItemID " & _
                             " AND OpeningDate >= " & " '" & strFromDt & "'" & _
                             " AND OpeningDate  < = " & "'" & strToDt & "'" & _
                             " AND topen.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "topen", "topen"))

                Case "-1", "0"

                    sbSql.Append(" SELECT PI.InvoiceDate as Date, mv.vendorname as Description, (pid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(pid.Qty * pid.NetRate as float) as InValue, 0 as OutValue " & _
                             " FROM  tpurchaseinvoice PI, tpurchaseinvoicedet pid, mpItem mi, mpvendor mv " & _
                             " WHERE PI.InvoiceID = pid.InvoiceID " & _
                             " AND pid.ItemID = mi.ItemID " & _
                             " AND PI.VendorID = mv.VendorID  " & _
                             " AND PI.InvoiceDate > = " & "'" & strFromDt & "'" & _
                             " AND PI.InvoiceDate < = " & "'" & strToDt & "'" & _
                             " AND PI.FinYrID=intFinYrID AND pid.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "PI", "pid"))

                    sbSql.Append(" UNION ALL SELECT Td.DCDate as Date, mv.vendorname as Description, (tid.Qty * case mi.SellLoose when 1 then mi.ItemsPerPack else 1 end ) as InQuantity, 0 as OutQuantity, CAST(tid.Qty * tid.PurchaseRate as float) as InValue, 0 as OutValue " & _
                                      " FROM  tDC Td, tdcdet tid, mpItem mi, mpvendor mv " & _
                                      " WHERE td.DCno = tid.DCno " & _
                                      " AND tid.ItemID = mi.ItemID " & _
                                      " AND Td.VendorID = mv.VendorID  AND td.DCno not in (SELECT DCNO FROM tPurchaseInvoice)" & _
                                      " AND Td.DCDate > = " & "'" & strFromDt & "'" & _
                                      " AND Td.DCDate < = " & "'" & strToDt & "'" & _
                                      " AND Td.FinYrID=intFinYrID AND tid.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "td", "tid"))

                    sbSql.Append(" UNION ALL SELECT tpur.ReturnDate as Date, mv.vendorname as Description, 0 as InQuantity, tpurd.Qty as OutQuantity, 0 as InValue, tpurd.Amount as OutValue " & _
                                      " FROM tpurreturn tpur, tpurreturndet tpurd, mpvendor mv, mpItem mi" & _
                                      " WHERE tpur.PurReturnID = tpurd.PurReturnID" & _
                                      " AND tpur.VendorID = mv.VendorID" & _
                                      " AND mi.ItemID = tpurd.ItemID" & _
                                      " AND tpur.ReturnDate  >= " & " '" & strFromDt & "'" & _
                                      " AND tpur.ReturnDate  < = " & "'" & strToDt & "'" & _
                                      " AND tpur.FinYrID=intFinYrID AND tpurd.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "tpur", "tpurd"))

                    sbSql.Append(" UNION ALL SELECT ts.SaleDate as Date, case when ts.CustID=0 then 'CounterSale' else p.FName + ' ' + p.LName end as Description, 0 as InQuantity, sd.Qty as OutQuantity, 0 as InValue, sd.Amount as OutValue " & _
                                       " FROM tsale ts, tsaledet sd, MpItem mi, mperson p, mcustomer mc, tstock st" & _
                                       " WHERE ts.SaleID = sd.SaleID " & _
                                       " AND sd.ItemID = mi.ItemID" & _
                                       " AND mc.CustID = ts.CustID " & _
                                       " AND p.personID = mc.personID " & _
                                       " AND sd.ItemID = st.ItemID" & _
                                       " AND st.ItemID = mi.ItemID " & _
                                       " AND st.CLRcode = sd.CLRcode " & _
                                       " AND ts.SaleDate >= " & " '" & strFromDt & "'" & _
                                       " AND ts.SaleDate < = " & "'" & strToDt & "'" & _
                                       " AND ts.FinYrID=intFinYrID AND sd.FinYrID=intFinYrID AND st.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "sd"))

                    sbSql.Append(" UNION ALL SELECT tsr.SaleReturnDate as Date,'SalesReturn' as Description, tsrd.Qty as InQuantity,  0 as OutQuantity,  tsrd.Amount as InValue, 0 as OutValue " & _
                                      " FROM tsalereturn tsr, tsalereturndet as tsrd, MpItem mi,tstock st " & _
                                      " WHERE tsr.SaleReturnID = tsrd.SaleReturnID " & _
                                      " AND tsrd.ItemID = mi.ItemID " & _
                                      " AND st.ItemID = mi.ItemID " & _
                                      " AND tsrd.ItemID = st.ItemID " & _
                                      " AND st.CLRcode = tsrd.CLRcode " & _
                                      " AND tsr.SaleReturnDate  >= " & " '" & strFromDt & "'" & _
                                      " AND tsr.SaleReturnDate  < = " & "'" & strToDt & "'" & _
                                      " AND tsr.FinYrID=intFinYrID AND tsrd.FinYrID=intFinYrID AND st.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "st", "tsrd"))

                    sbSql.Append(" UNION ALL SELECT topen.OpeningDate as Date, 'OpeningStock' as Description, topen.Qty as InQuantity," & _
                                      " 0 as OutQuantity, (Qty * Purchaserate) as Invalue, 0 as OutValue " & _
                                      " FROM tOpeningStock topen,  mpItem mi" & _
                                      " WHERE mi.itemID= topen.ItemID " & _
                                      " AND OpeningDate >= " & " '" & strFromDt & "'" & _
                                      " AND OpeningDate  < = " & "'" & strToDt & "'" & _
                                      " AND topen.FinYrID=intFinYrID")

                    sbSql.Append(QueryBuild(objProdTrans, "mi", "topen", "topen"))

            End Select

            Return DbHelper.ExecuteDataReader(sbSql.ToString())


        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProdTrans", "GetTrans")
            Return Nothing
        End Try
    End Function

End Class
